#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import sys, datetime
import pandas as pd
import numpy as np
# import xlsxwriter
# from scipy.optimize import minimize
# from scipy.optimize import Bounds
# import scipy.optimize as opt



from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from model.AbstractDPJob import AbstractDPJob


class MODELJob(AbstractDPJob):


    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,p_ver=None,p_type=None):


        super(MODELJob, self).__init__(p_config=p_config,
                                        p_db_conn_mpp=p_db_conn_mpp,
                                        p_db_conn_rds=None,
                                        p_db_conn_dbprod7=None,
                                        p_unit=None,
                                        p_account=None,
                                        p_cost_center=None,
                                        p_account_period_start=None,
                                        p_account_period_end=None)
        self.ver = p_ver
        self.type = p_type
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(MODELJob, self).do_execute()

        #铁水温度成分停吹温度影响 sheet1
        #发热剂对石灰及铁损影响 sheet2
        #矿石用量影响 sheet3
        #铁水废钢价格影响 sheet4
        #过程数据汇总 sheet5
        #各种价格及参数输入画面 sheet6
        #计算总结果 sheet7



        #type = 1时，计算当前铁水比的炼钢成本变化值
        #type = 2时，保持铁水量及本月计划钢产量不变，计算最优铁水比，使得炼钢成本变化值最小
        #type = 3时，当没有铁水比范围时，加入一吨铁水，如何分配到一炼钢二炼钢，使得炼钢成本变化值最小
        #type = 4时，在铁水比范围内，加入一炼钢二炼钢分别多少铁水，使得炼钢成本变化值最小
        ### type = 1时，根据铁水，钢水，通过公式计算当前铁水比，再与基准铁水比进行比较盈亏
        ### type = 2时，根据铁水，钢水，计算最优铁水比
        ### type = 3时，根据铁水，钢水，通过公式计算当前铁水比，当增加一定量的铁水（1）时，如何分配给一炼钢二炼钢各多少时最优
        type = self.type
        #code = 0
        #建好表后打开
        sql = " SELECT IND_NAME,IND_TYPE,IND_VALUE,VER FROM BGTAMAQA.T_ADS_WH_ZZQM_GXCB_MODELINDEX " \
              " WHERE 1=1 " \
              " AND VER = '%s'" % (self.ver)


        df = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        success = df.empty is False
        if success is False:
            return 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
        df.columns = df.columns.str.upper()
        sheet6_D5 = df[(df['IND_NAME'] == '铁水成本') & (df['IND_TYPE'] == '原料成本')]
        sheet6_D5 = sheet6_D5['IND_VALUE'].values[0]
        sheet6_D6 = df[(df['IND_NAME'] == '外购中混废钢') & (df['IND_TYPE'] == '原料成本')]
        sheet6_D6 = sheet6_D6['IND_VALUE'].values[0]
        sheet6_D9 = df[(df['IND_NAME'] == '外购石灰') & (df['IND_TYPE'] == '原料成本')]
        sheet6_D9 = sheet6_D9['IND_VALUE'].values[0]
        sheet6_D10 = df[(df['IND_NAME'] == '硅铁碳球发热剂') & (df['IND_TYPE'] == '原料成本')]
        sheet6_D10 = sheet6_D10['IND_VALUE'].values[0]
        sheet6_D11 = df[(df['IND_NAME'] == '铁矿石') & (df['IND_TYPE'] == '原料成本')]
        sheet6_D11 = sheet6_D11['IND_VALUE'].values[0]
        sheet6_D12 = df[(df['IND_NAME'] == '主原料钢铁料成本') & (df['IND_TYPE'] == '原料成本')]
        sheet6_D12 = sheet6_D12['IND_VALUE'].values[0]
        sheet6_B16 = df[(df['IND_NAME'] == 'T.Fe') & (df['IND_TYPE'] == '转炉用铁矿石成分')]
        sheet6_B16 = sheet6_B16['IND_VALUE'].values[0]
        sheet6_C16 = df[(df['IND_NAME'] == 'SiO2') & (df['IND_TYPE'] == '转炉用铁矿石成分')]
        sheet6_C16 = sheet6_C16['IND_VALUE'].values[0]
        sheet6_B25 = df[(df['IND_NAME'] == '铁水Si') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_B25 = sheet6_B25['IND_VALUE'].values[0]
        sheet6_B26 = df[(df['IND_NAME'] == '铁水Si') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_B26 = sheet6_B26['IND_VALUE'].values[0]
        sheet6_C25 = df[(df['IND_NAME'] == '入炉温度') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_C25 = sheet6_C25['IND_VALUE'].values[0]
        sheet6_C26 = df[(df['IND_NAME'] == '入炉温度') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_C26 = sheet6_C26['IND_VALUE'].values[0]
        sheet6_C30 = df[(df['IND_NAME'] == '停吹温度') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_C30 = sheet6_C30['IND_VALUE'].values[0]
        sheet6_C31 = df[(df['IND_NAME'] == '停吹温度') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_C31 = sheet6_C31['IND_VALUE'].values[0]
        sheet6_B35 = df[(df['IND_NAME'] == '装入碱度') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_B35 = sheet6_B35['IND_VALUE'].values[0]
        sheet6_B36 = df[(df['IND_NAME'] == '装入碱度') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_B36 = sheet6_B36['IND_VALUE'].values[0]
        sheet6_C35 = df[(df['IND_NAME'] == '炉渣(T.Fe)含量') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_C35 = sheet6_C35['IND_VALUE'].values[0] /100
        sheet6_C36 = df[(df['IND_NAME'] == '炉渣(T.Fe)含量') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_C36 = sheet6_C36['IND_VALUE'].values[0] /100
        sheet2_C3 = df[(df['IND_NAME'] == '发热剂Si含量') & (df['IND_TYPE'] == '一炼钢')]
        sheet2_C3 = sheet2_C3['IND_VALUE'].values[0]
        sheet2_C4 = df[(df['IND_NAME'] == '发热剂Si含量') & (df['IND_TYPE'] == '二炼钢')]
        sheet2_C4 = sheet2_C4['IND_VALUE'].values[0]
        sheet2_D3 = df[(df['IND_NAME'] == '发热剂加入量') & (df['IND_TYPE'] == '一炼钢')]
        sheet2_D3 = sheet2_D3['IND_VALUE'].values[0]
        sheet2_D4 = df[(df['IND_NAME'] == '发热剂加入量') & (df['IND_TYPE'] == '二炼钢')]
        sheet2_D4 = sheet2_D4['IND_VALUE'].values[0]
        sheet2_C9 = df[(df['IND_NAME'] == '过吹') & (df['IND_TYPE'] == '一炼钢')]
        sheet2_C9 = sheet2_C9['IND_VALUE'].values[0]
        sheet2_C10 = df[(df['IND_NAME'] == '过吹') & (df['IND_TYPE'] == '二炼钢')]
        sheet2_C10 = sheet2_C10['IND_VALUE'].values[0]
        sheet4_B6 = df[(df['IND_NAME'] == '收得率') & (df['IND_TYPE'] == '废钢综合')]
        sheet4_B6 = sheet4_B6['IND_VALUE'].values[0]
        sheet4_B7 = df[(df['IND_NAME'] == '收得率') & (df['IND_TYPE'] == '铁水成本')]
        sheet4_B7 = sheet4_B7['IND_VALUE'].values[0]
        sheet6_D20 = df[(df['IND_NAME'] == '计划铁水比') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_D20 = sheet6_D20['IND_VALUE'].values[0]
        sheet6_D21 = df[(df['IND_NAME'] == '计划铁水比') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_D21 = sheet6_D21['IND_VALUE'].values[0]
        sheet6_B20 = df[(df['IND_NAME'] == '铁水量') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_B20 = sheet6_B20['IND_VALUE'].values[0]
        sheet6_B21 = df[(df['IND_NAME'] == '铁水量') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_B21 = sheet6_B21['IND_VALUE'].values[0]
        sheet7_B7 = df[(df['IND_NAME'] == '本月计划钢产量') & (df['IND_TYPE'] == '一炼钢')]
        sheet7_B7 = sheet7_B7['IND_VALUE'].values[0]
        sheet7_C7 = df[(df['IND_NAME'] == '本月计划钢产量') & (df['IND_TYPE'] == '二炼钢')]
        sheet7_C7 = sheet7_C7['IND_VALUE'].values[0]
        sheet6_F20 = df[(df['IND_NAME'] == '连铸收得率') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_F20 = sheet6_F20['IND_VALUE'].values[0]
        sheet6_F21 = df[(df['IND_NAME'] == '连铸收得率') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_F21 = sheet6_F21['IND_VALUE'].values[0]
        sheet6_E20 = df[(df['IND_NAME'] == '计划钢水钢铁料消耗') & (df['IND_TYPE'] == '一炼钢')]
        sheet6_E20 = sheet6_E20['IND_VALUE'].values[0]
        sheet6_E21 = df[(df['IND_NAME'] == '计划钢水钢铁料消耗') & (df['IND_TYPE'] == '二炼钢')]
        sheet6_E21 = sheet6_E21['IND_VALUE'].values[0]

        # sheet6_D5 = 3710
        # sheet6_D6 = 3280
        # sheet6_D9 = 508.5
        # sheet6_D10 = 2000
        # sheet6_D11 = 1253.6
        # sheet6_D12 = 2992.78307679633
        # sheet6_B16 = 62.8
        # sheet6_C16 = 3.5
        # sheet6_B25 = 0.4005
        # sheet6_B26 = 0.4174
        # sheet6_C25 = 1349.9
        # sheet6_C26 = 1347.2
        # sheet6_C30 = 1652.9
        # sheet6_C31 = 1661.7
        # sheet6_B35 = 3
        # sheet6_B36 = 3
        # sheet6_C35 = 0.2175
        # sheet6_C36 = 0.1777
        # sheet2_C3 = 0.18
        # sheet2_C4 = 0.18
        # sheet2_D3 = 700
        # sheet2_D4 = 700
        # sheet2_C9 = 0.28
        # sheet2_C10 = 0.25
        # sheet4_B6 = 0.935
        # sheet4_B7 = 0.945
        # sheet6_D20 = 0.8301
        # sheet6_D21 = 0.8593
        # sheet6_B20 = 58.87
        # sheet6_B21 = 53.04
        # sheet7_B7 = 63.58
        # sheet7_C7 = 55.94
        # sheet6_F20 = 0.966863
        # sheet6_F21 = 0.968649
        # sheet6_E20 = 1078.56
        # sheet6_E21 = 1068.90
















        if type == 2:
            # degree = 0.001
            # act_degree = 0.001 / degree
            min_result = 10000
            for sheet7_B6_R in range(8100, 8360, 1):
                sheet7_B6 = sheet7_B6_R / 10000
                # print(result)
                for sheet7_C6_R in range(8400, 8660, 1):
                    sheet7_C6 = sheet7_C6_R / 10000
                    # sheet6_B20 = 58.87
                    # sheet6_B21 = 53.04
                    sheet7_B4 = sheet6_B20
                    sheet7_C4 = sheet6_B21

                    # sheet7_B7 = 63.58
                    # sheet7_C7 = 55.94
                    # sheet6_F20 = 0.966863
                    # sheet6_F21 = 0.968649
                    sheet7_B9 = sheet6_F20
                    sheet7_C9 = sheet6_F21
                    sheet7_B8 = sheet7_B7 / sheet7_B9
                    sheet7_C8 = sheet7_C7 / sheet7_C9
                    # sheet6_E20 = 1078.56
                    # sheet6_E21 = 1068.90
                    code = 0
                    sheet7_D13 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8, sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                                   , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7, sheet7_C7=sheet7_C7,
                                                   sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21, sheet7_B9=sheet7_B9, sheet7_C9=sheet7_C9,
                                                   sheet6_E20=sheet6_E20, sheet6_E21=sheet6_E21
                                                   , sheet6_D5=sheet6_D5, sheet6_D6=sheet6_D6, sheet6_D9=sheet6_D9,
                                                   sheet6_D10=sheet6_D10, sheet6_D11=sheet6_D11, sheet6_D12=sheet6_D12
                                                   , sheet6_B16=sheet6_B16, sheet6_C16=sheet6_C16,
                                                   sheet6_B25=sheet6_B25, sheet6_B26=sheet6_B26, sheet6_C25=sheet6_C25,
                                                   sheet6_C26=sheet6_C26
                                                   , sheet6_C30=sheet6_C30, sheet6_C31=sheet6_C31,
                                                   sheet6_B35=sheet6_B35, sheet6_B36=sheet6_B36, sheet6_C35=sheet6_C35,
                                                   sheet6_C36=sheet6_C36
                                                   , sheet2_C3=sheet2_C3, sheet2_C4=sheet2_C4, sheet2_D3=sheet2_D3,
                                                   sheet2_D4=sheet2_D4, sheet2_C9=sheet2_C9, sheet2_C10=sheet2_C10
                                                   , sheet4_B6=sheet4_B6, sheet4_B7=sheet4_B7, sheet6_D20=sheet6_D20,
                                                   sheet6_D21=sheet6_D21,code=code
                                                   )




                    result = sheet7_D13
                    if result < min_result:
                        min_result = result
                        best_B6 = sheet7_B6
                        best_C6 = sheet7_C6

            # print(min_result)
            # print(best_B6)
            # print(best_C6)
            sheet7_B6 = best_B6
            sheet7_C6 = best_C6
            # sheet6_B20 = 58.87
            # sheet6_B21 = 53.04
            sheet7_B4 = sheet6_B20
            sheet7_C4 = sheet6_B21

            # sheet7_B7 = 63.58
            # sheet7_C7 = 55.94
            # sheet6_F20 = 0.966863
            # sheet6_F21 = 0.968649
            sheet7_B9 = sheet6_F20
            sheet7_C9 = sheet6_F21
            sheet7_B8 = sheet7_B7 / sheet7_B9
            sheet7_C8 = sheet7_C7 / sheet7_C9
            # sheet6_E20 = 1078.56
            # sheet6_E21 = 1068.90
            code = 1
            sheet7_B5,sheet7_C5,sheet7_B13,sheet7_C13,sheet7_D13,sheet7_B14,sheet7_C14,sheet7_D14 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8,
                                           sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                           , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7,
                                           sheet7_C7=sheet7_C7,
                                           sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21, sheet7_B9=sheet7_B9,
                                           sheet7_C9=sheet7_C9,
                                           sheet6_E20=sheet6_E20, sheet6_E21=sheet6_E21,sheet6_D5 = sheet6_D5,sheet6_D6 = sheet6_D6,sheet6_D9 = sheet6_D9,sheet6_D10 = sheet6_D10,sheet6_D11 = sheet6_D11,sheet6_D12 = sheet6_D12
        , sheet6_B16 = sheet6_B16,sheet6_C16 = sheet6_C16,sheet6_B25 = sheet6_B25,sheet6_B26 = sheet6_B26,sheet6_C25 = sheet6_C25,sheet6_C26 = sheet6_C26
        , sheet6_C30 = sheet6_C30,sheet6_C31 = sheet6_C31,sheet6_B35 = sheet6_B35,sheet6_B36 = sheet6_B36,sheet6_C35 = sheet6_C35,sheet6_C36 = sheet6_C36
        , sheet2_C3 = sheet2_C3,sheet2_C4 = sheet2_C4,sheet2_D3 = sheet2_D3,sheet2_D4 = sheet2_D4,sheet2_C9 = sheet2_C9,sheet2_C10 = sheet2_C10
        , sheet4_B6 = sheet4_B6,sheet4_B7 = sheet4_B7,sheet6_D20 = sheet6_D20,sheet6_D21 = sheet6_D21,code=code)
            type2_best_B6 = best_B6
            type2_best_C6 = best_C6
            type3_best_B4_ADD = None
            type3_best_C4_ADD = None
            type4_best_B4_ADD = None
            type4_best_C4_ADD = None
            type4_best_total_ADD = None






        if type == 4:
            ADD_IRON = 1
            degree = 1000
            min_result = 10000
            sheet7_B6_max = 0.835
            sheet7_B6_min = 0.810
            sheet7_C6_max = 0.865
            sheet7_C6_min = 0.840

            # sheet6_B20 = 58.87
            # sheet6_B21 = 53.04
            sheet7_B4 = sheet6_B20
            sheet7_C4 = sheet6_B21

            # sheet7_B7 = 63.58
            # sheet7_C7 = 55.94
            # sheet6_F20 = 0.966863
            # sheet6_F21 = 0.968649
            sheet7_B9 = sheet6_F20
            sheet7_C9 = sheet6_F21
            sheet7_B8 = sheet7_B7 / sheet7_B9
            sheet7_C8 = sheet7_C7 / sheet7_C9
            # sheet6_E20 = 1078.56
            # sheet6_E21 = 1068.90
            sheet7_B4_max = (sheet7_B8 * sheet6_E20 / 1000) * sheet7_B6_max
            sheet7_C4_max = (sheet7_C8 * sheet6_E21 / 1000) * sheet7_C6_max
            sheet7_B4_min = (sheet7_B8 * sheet6_E20 / 1000) * sheet7_B6_min
            sheet7_C4_min = (sheet7_C8 * sheet6_E21 / 1000) * sheet7_C6_min

            ADD_B4_max = sheet7_B4_max - sheet7_B4
            ADD_B4_min = sheet7_B4_min - sheet7_B4
            ADD_C4_max = sheet7_C4_max - sheet7_C4
            ADD_C4_min = sheet7_C4_min - sheet7_C4
            # 加入总铁水量x
            # 加入一炼钢铁水量y
            # 加入二炼钢铁水量x-y
            # for x range 0， max1+max2
            # for y range max(0,x-max2),min(x,max1)
            max1 = ADD_B4_max
            max2 = ADD_C4_max
            upper_x = max1 + max2
            upper_x = round(upper_x * 1000)
            for x in range(0, upper_x, 1):
                if x - max2*1000 > 0:
                    lower_y = x - round(max2*1000)
                else:
                    lower_y = 0
                if x > max1*1000:
                    upper_y = round(max1*1000)
                else:
                    upper_y = x
                for y in range(lower_y,upper_y,1):
                    sheet7_B4_ADD = y / 1000
                    sheet7_C4_ADD = (x - y) / 1000
                    # sheet7_C4_ADD = 1 - sheet7_B4_ADD

                    # sheet6_B20 = 58.87
                    # sheet6_B21 = 53.04
                    sheet7_B4 = sheet6_B20
                    sheet7_C4 = sheet6_B21

                    sheet7_B4 = sheet7_B4 + sheet7_B4_ADD
                    sheet7_C4 = sheet7_C4 + sheet7_C4_ADD

                    # sheet7_B7 = 63.58
                    # sheet7_C7 = 55.94
                    # sheet6_F20 = 0.966863
                    # sheet6_F21 = 0.968649
                    sheet7_B9 = sheet6_F20
                    sheet7_C9 = sheet6_F21
                    sheet7_B8 = sheet7_B7 / sheet7_B9
                    sheet7_C8 = sheet7_C7 / sheet7_C9
                    # sheet6_E20 = 1078.56
                    # sheet6_E21 = 1068.90

                    # 根据铁水比公式得到的铁水比
                    sheet7_B6 = sheet7_B4 / (sheet7_B8 * sheet6_E20 / 1000)
                    sheet7_C6 = sheet7_C4 / (sheet7_C8 * sheet6_E21 / 1000)
                    code = 0
                    #print(code)
                    sheet7_D13 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8,
                                                   sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                                   , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7,
                                                   sheet7_C7=sheet7_C7, sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21,
                                                   sheet7_B9=sheet7_B9, sheet7_C9=sheet7_C9, sheet6_E20=sheet6_E20,
                                                   sheet6_E21=sheet6_E21
                                                   , sheet6_D5=sheet6_D5, sheet6_D6=sheet6_D6, sheet6_D9=sheet6_D9,
                                                   sheet6_D10=sheet6_D10, sheet6_D11=sheet6_D11, sheet6_D12=sheet6_D12
                                                   , sheet6_B16=sheet6_B16, sheet6_C16=sheet6_C16,
                                                   sheet6_B25=sheet6_B25, sheet6_B26=sheet6_B26, sheet6_C25=sheet6_C25,
                                                   sheet6_C26=sheet6_C26
                                                   , sheet6_C30=sheet6_C30, sheet6_C31=sheet6_C31,
                                                   sheet6_B35=sheet6_B35, sheet6_B36=sheet6_B36, sheet6_C35=sheet6_C35,
                                                   sheet6_C36=sheet6_C36
                                                   , sheet2_C3=sheet2_C3, sheet2_C4=sheet2_C4, sheet2_D3=sheet2_D3,
                                                   sheet2_D4=sheet2_D4, sheet2_C9=sheet2_C9, sheet2_C10=sheet2_C10
                                                   , sheet4_B6=sheet4_B6, sheet4_B7=sheet4_B7, sheet6_D20=sheet6_D20,
                                                   sheet6_D21=sheet6_D21,code=code
                                                   )
                    result = sheet7_D13
                    if result < min_result:
                        min_result = result
                        best_x = x
                        best_y = y
            # print(min_result)
            # print(best_x)
            # print(best_y)
            sheet7_B4_ADD = best_y / 1000
            sheet7_C4_ADD = (best_x - best_y) / 1000
            # sheet6_B20 = 58.87
            # sheet6_B21 = 53.04
            sheet7_B4 = sheet6_B20
            sheet7_C4 = sheet6_B21

            sheet7_B4 = sheet7_B4 + sheet7_B4_ADD
            sheet7_C4 = sheet7_C4 + sheet7_C4_ADD

            # sheet7_B7 = 63.58
            # sheet7_C7 = 55.94
            # sheet6_F20 = 0.966863
            # sheet6_F21 = 0.968649
            sheet7_B9 = sheet6_F20
            sheet7_C9 = sheet6_F21
            sheet7_B8 = sheet7_B7 / sheet7_B9
            sheet7_C8 = sheet7_C7 / sheet7_C9


            # 根据铁水比公式得到的铁水比 # sheet6_E20 = 1078.56
            #             # sheet6_E21 = 1068.90
            sheet7_B6 = sheet7_B4 / (sheet7_B8 * sheet6_E20 / 1000)
            sheet7_C6 = sheet7_C4 / (sheet7_C8 * sheet6_E21 / 1000)
            code = 1
            sheet7_B5,sheet7_C5,sheet7_B13,sheet7_C13,sheet7_D13,sheet7_B14,sheet7_C14,sheet7_D14 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8,
                                           sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                           , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7,
                                           sheet7_C7=sheet7_C7,
                                           sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21, sheet7_B9=sheet7_B9,
                                           sheet7_C9=sheet7_C9,
                                           sheet6_E20=sheet6_E20, sheet6_E21=sheet6_E21
                                                                                                                      ,
                                                                                                                      sheet6_D5=sheet6_D5,
                                                                                                                      sheet6_D6=sheet6_D6,
                                                                                                                      sheet6_D9=sheet6_D9,
                                                                                                                      sheet6_D10=sheet6_D10,
                                                                                                                      sheet6_D11=sheet6_D11,
                                                                                                                      sheet6_D12=sheet6_D12
                                                                                                                      ,
                                                                                                                      sheet6_B16=sheet6_B16,
                                                                                                                      sheet6_C16=sheet6_C16,
                                                                                                                      sheet6_B25=sheet6_B25,
                                                                                                                      sheet6_B26=sheet6_B26,
                                                                                                                      sheet6_C25=sheet6_C25,
                                                                                                                      sheet6_C26=sheet6_C26
                                                                                                                      ,
                                                                                                                      sheet6_C30=sheet6_C30,
                                                                                                                      sheet6_C31=sheet6_C31,
                                                                                                                      sheet6_B35=sheet6_B35,
                                                                                                                      sheet6_B36=sheet6_B36,
                                                                                                                      sheet6_C35=sheet6_C35,
                                                                                                                      sheet6_C36=sheet6_C36
                                                                                                                      ,
                                                                                                                      sheet2_C3=sheet2_C3,
                                                                                                                      sheet2_C4=sheet2_C4,
                                                                                                                      sheet2_D3=sheet2_D3,
                                                                                                                      sheet2_D4=sheet2_D4,
                                                                                                                      sheet2_C9=sheet2_C9,
                                                                                                                      sheet2_C10=sheet2_C10
                                                                                                                      ,
                                                                                                                      sheet4_B6=sheet4_B6,
                                                                                                                      sheet4_B7=sheet4_B7,
                                                                                                                      sheet6_D20=sheet6_D20,
                                                                                                                      sheet6_D21=sheet6_D21,code=code
                                                                                                                      )
            type2_best_B6 = None
            type2_best_C6 = None
            type3_best_B4_ADD = None
            type3_best_C4_ADD = None
            type4_best_B4_ADD = sheet7_B4_ADD
            type4_best_C4_ADD = sheet7_C4_ADD
            type4_best_total_ADD = sheet7_B4_ADD + sheet7_C4_ADD

        if type == 3:
            ADD_IRON = 1
            degree = 1000
            min_result = 10000


            for sheet7_B4_ADD_R in range(0, ADD_IRON*degree+1, 1):
                sheet7_B4_ADD = sheet7_B4_ADD_R / degree
                sheet7_C4_ADD = (ADD_IRON*degree - sheet7_B4_ADD_R) / degree
                # sheet7_C4_ADD = 1 - sheet7_B4_ADD

                # sheet6_B20 = 58.87
                # sheet6_B21 = 53.04
                sheet7_B4 = sheet6_B20
                sheet7_C4 = sheet6_B21

                sheet7_B4 = sheet7_B4 + sheet7_B4_ADD
                sheet7_C4 = sheet7_C4 + sheet7_C4_ADD

                # sheet7_B7 = 63.58
                # sheet7_C7 = 55.94
                # sheet6_F20 = 0.966863
                # sheet6_F21 = 0.968649
                sheet7_B9 = sheet6_F20
                sheet7_C9 = sheet6_F21
                sheet7_B8 = sheet7_B7 / sheet7_B9
                sheet7_C8 = sheet7_C7 / sheet7_C9
                # sheet6_E20 = 1078.56
                # sheet6_E21 = 1068.90


                # 根据铁水比公式得到的铁水比
                sheet7_B6 = sheet7_B4 / (sheet7_B8 * sheet6_E20 / 1000)
                sheet7_C6 = sheet7_C4 / (sheet7_C8 * sheet6_E21 / 1000)
                code = 0
                sheet7_D13 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8,
                                               sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                               , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7,
                                               sheet7_C7=sheet7_C7, sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21,
                                               sheet7_B9=sheet7_B9, sheet7_C9=sheet7_C9, sheet6_E20=sheet6_E20, sheet6_E21=sheet6_E21
                                               , sheet6_D5=sheet6_D5, sheet6_D6=sheet6_D6, sheet6_D9=sheet6_D9,
                                               sheet6_D10=sheet6_D10, sheet6_D11=sheet6_D11, sheet6_D12=sheet6_D12
                                               , sheet6_B16=sheet6_B16, sheet6_C16=sheet6_C16, sheet6_B25=sheet6_B25,
                                               sheet6_B26=sheet6_B26, sheet6_C25=sheet6_C25, sheet6_C26=sheet6_C26
                                               , sheet6_C30=sheet6_C30, sheet6_C31=sheet6_C31, sheet6_B35=sheet6_B35,
                                               sheet6_B36=sheet6_B36, sheet6_C35=sheet6_C35, sheet6_C36=sheet6_C36
                                               , sheet2_C3=sheet2_C3, sheet2_C4=sheet2_C4, sheet2_D3=sheet2_D3,
                                               sheet2_D4=sheet2_D4, sheet2_C9=sheet2_C9, sheet2_C10=sheet2_C10
                                               , sheet4_B6=sheet4_B6, sheet4_B7=sheet4_B7, sheet6_D20=sheet6_D20,
                                               sheet6_D21=sheet6_D21,code=code
                                               )


                result = sheet7_D13
                if result < min_result:
                    min_result = result
                    best_B4_ADD = sheet7_B4_ADD
                    best_C4_ADD = sheet7_C4_ADD

            # print(min_result)
            # print(best_B4_ADD)
            # print(best_C4_ADD)

            # sheet6_B20 = 58.87
            # sheet6_B21 = 53.04
            sheet7_B4 = sheet6_B20
            sheet7_C4 = sheet6_B21

            sheet7_B4 = sheet7_B4 + best_B4_ADD
            sheet7_C4 = sheet7_C4 + best_C4_ADD

            # sheet7_B7 = 63.58
            # sheet7_C7 = 55.94
            # sheet6_F20 = 0.966863
            # sheet6_F21 = 0.968649
            sheet7_B9 = sheet6_F20
            sheet7_C9 = sheet6_F21
            sheet7_B8 = sheet7_B7 / sheet7_B9
            sheet7_C8 = sheet7_C7 / sheet7_C9
            # sheet6_E20 = 1078.56
            # sheet6_E21 = 1068.90

            # 根据铁水比公式得到的铁水比
            sheet7_B6 = sheet7_B4 / (sheet7_B8 * sheet6_E20 / 1000)
            sheet7_C6 = sheet7_C4 / (sheet7_C8 * sheet6_E21 / 1000)
            code = 1
            sheet7_B5,sheet7_C5,sheet7_B13,sheet7_C13,sheet7_D13,sheet7_B14,sheet7_C14,sheet7_D14 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8,
                                           sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                           , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7,
                                           sheet7_C7=sheet7_C7,
                                           sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21, sheet7_B9=sheet7_B9,
                                           sheet7_C9=sheet7_C9,
                                           sheet6_E20=sheet6_E20, sheet6_E21=sheet6_E21
                                                                                                                      ,
                                                                                                                      sheet6_D5=sheet6_D5,
                                                                                                                      sheet6_D6=sheet6_D6,
                                                                                                                      sheet6_D9=sheet6_D9,
                                                                                                                      sheet6_D10=sheet6_D10,
                                                                                                                      sheet6_D11=sheet6_D11,
                                                                                                                      sheet6_D12=sheet6_D12
                                                                                                                      ,
                                                                                                                      sheet6_B16=sheet6_B16,
                                                                                                                      sheet6_C16=sheet6_C16,
                                                                                                                      sheet6_B25=sheet6_B25,
                                                                                                                      sheet6_B26=sheet6_B26,
                                                                                                                      sheet6_C25=sheet6_C25,
                                                                                                                      sheet6_C26=sheet6_C26
                                                                                                                      ,
                                                                                                                      sheet6_C30=sheet6_C30,
                                                                                                                      sheet6_C31=sheet6_C31,
                                                                                                                      sheet6_B35=sheet6_B35,
                                                                                                                      sheet6_B36=sheet6_B36,
                                                                                                                      sheet6_C35=sheet6_C35,
                                                                                                                      sheet6_C36=sheet6_C36
                                                                                                                      ,
                                                                                                                      sheet2_C3=sheet2_C3,
                                                                                                                      sheet2_C4=sheet2_C4,
                                                                                                                      sheet2_D3=sheet2_D3,
                                                                                                                      sheet2_D4=sheet2_D4,
                                                                                                                      sheet2_C9=sheet2_C9,
                                                                                                                      sheet2_C10=sheet2_C10
                                                                                                                      ,
                                                                                                                      sheet4_B6=sheet4_B6,
                                                                                                                      sheet4_B7=sheet4_B7,
                                                                                                                      sheet6_D20=sheet6_D20,
                                                                                                                      sheet6_D21=sheet6_D21,code=code
                                                                                                                      )
            type2_best_B6 = None
            type2_best_C6 = None
            type3_best_B4_ADD = best_B4_ADD
            type3_best_C4_ADD = best_C4_ADD
            type4_best_B4_ADD = None
            type4_best_C4_ADD = None
            type4_best_total_ADD = None




        if type == 1:
                # sheet6_B20 = 58.87
                # sheet6_B21 = 53.04
                sheet7_B4 = sheet6_B20
                sheet7_C4 = sheet6_B21

                # sheet7_B7 = 63.58
                # sheet7_C7 = 55.94
                # sheet6_F20 = 0.966863
                # sheet6_F21 = 0.968649
                sheet7_B9 = sheet6_F20
                sheet7_C9 = sheet6_F21
                sheet7_B8 = sheet7_B7 / sheet7_B9
                sheet7_C8 = sheet7_C7 / sheet7_C9
                # sheet6_E20 = 1078.56
                # sheet6_E21 = 1068.90

                # 基准值
                # sheet7_B6 = 0.8301
                # sheet7_C6 = 0.8593
                # 随机输入的两个铁水比参数
                # sheet7_B6 = 0.82
                # sheet7_C6 = 0.86
                # 根据铁水比公式得到的铁水比
                sheet7_B6 = sheet7_B4 / (sheet7_B8 * sheet6_E20 / 1000)
                sheet7_C6 = sheet7_C4 / (sheet7_C8 * sheet6_E21 / 1000)

                code = 1
                sheet7_B5,sheet7_C5,sheet7_B13,sheet7_C13,sheet7_D13,sheet7_B14,sheet7_C14,sheet7_D14 = self.__step_S7D13(sheet7_B6=sheet7_B6, sheet7_C6=sheet7_C6, sheet7_B8=sheet7_B8,
                                               sheet7_C8=sheet7_C8, sheet7_B4=sheet7_B4, sheet7_C4=sheet7_C4
                                               , sheet6_B20=sheet6_B20, sheet6_B21=sheet6_B21, sheet7_B7=sheet7_B7,
                                               sheet7_C7=sheet7_C7, sheet6_F20=sheet6_F20, sheet6_F21=sheet6_F21,
                                               sheet7_B9=sheet7_B9, sheet7_C9=sheet7_C9, sheet6_E20=sheet6_E20,
                                               sheet6_E21=sheet6_E21
                                                                                                                          ,
                                                                                                                          sheet6_D5=sheet6_D5,
                                                                                                                          sheet6_D6=sheet6_D6,
                                                                                                                          sheet6_D9=sheet6_D9,
                                                                                                                          sheet6_D10=sheet6_D10,
                                                                                                                          sheet6_D11=sheet6_D11,
                                                                                                                          sheet6_D12=sheet6_D12
                                                                                                                          ,
                                                                                                                          sheet6_B16=sheet6_B16,
                                                                                                                          sheet6_C16=sheet6_C16,
                                                                                                                          sheet6_B25=sheet6_B25,
                                                                                                                          sheet6_B26=sheet6_B26,
                                                                                                                          sheet6_C25=sheet6_C25,
                                                                                                                          sheet6_C26=sheet6_C26
                                                                                                                          ,
                                                                                                                          sheet6_C30=sheet6_C30,
                                                                                                                          sheet6_C31=sheet6_C31,
                                                                                                                          sheet6_B35=sheet6_B35,
                                                                                                                          sheet6_B36=sheet6_B36,
                                                                                                                          sheet6_C35=sheet6_C35,
                                                                                                                          sheet6_C36=sheet6_C36
                                                                                                                          ,
                                                                                                                          sheet2_C3=sheet2_C3,
                                                                                                                          sheet2_C4=sheet2_C4,
                                                                                                                          sheet2_D3=sheet2_D3,
                                                                                                                          sheet2_D4=sheet2_D4,
                                                                                                                          sheet2_C9=sheet2_C9,
                                                                                                                          sheet2_C10=sheet2_C10
                                                                                                                          ,
                                                                                                                          sheet4_B6=sheet4_B6,
                                                                                                                          sheet4_B7=sheet4_B7,
                                                                                                                          sheet6_D20=sheet6_D20,
                                                                                                                          sheet6_D21=sheet6_D21,code=code
                                                                                                                          )
                type2_best_B6 = None
                type2_best_C6 = None
                type3_best_B4_ADD = None
                type3_best_C4_ADD = None
                type4_best_B4_ADD = None
                type4_best_C4_ADD = None
                type4_best_total_ADD = None

        sheet7_D4 = sheet7_B4 + sheet7_C4
        sheet7_D7 = sheet7_B7 + sheet7_C7
        sheet7_D8 = sheet7_B8 + sheet7_C8
        sheet7_B4 = round(sheet7_B4, 4)
        sheet7_C4 = round(sheet7_C4, 4)
        sheet7_D4 = round(sheet7_D4, 4)
        sheet7_B5 = round(sheet7_B5, 4)
        sheet7_C5 = round(sheet7_C5, 4)
        sheet7_B6 = round(sheet7_B6, 4)
        sheet7_C6 = round(sheet7_C6, 4)
        sheet7_B7 = round(sheet7_B7, 4)
        sheet7_C7 = round(sheet7_C7, 4)
        sheet7_D7 = round(sheet7_D7, 4)
        sheet7_B8 = round(sheet7_B8, 4)
        sheet7_C8 = round(sheet7_C8, 4)
        sheet7_D8 = round(sheet7_D8, 4)
        sheet7_B9 = round(sheet7_B9, 4)
        sheet7_C9 = round(sheet7_C9, 4)
        sheet7_B13 = round(sheet7_B13, 4)
        sheet7_C13 = round(sheet7_C13, 4)
        sheet7_D13 = round(sheet7_D13, 4)
        sheet7_B14 = round(sheet7_B14, 4)
        sheet7_C14 = round(sheet7_C14, 4)
        sheet7_D14 = round(sheet7_D14, 4)




        return sheet7_B4, sheet7_C4,sheet7_D4, sheet7_B5, sheet7_C5, sheet7_B6, sheet7_C6, sheet7_B7, sheet7_C7, sheet7_D7, sheet7_B8, sheet7_C8, sheet7_D8, sheet7_B9, sheet7_C9, sheet7_B13, sheet7_C13, sheet7_D13, sheet7_B14, sheet7_C14, sheet7_D14, type2_best_B6, type2_best_C6, type3_best_B4_ADD, type3_best_C4_ADD, type4_best_B4_ADD, type4_best_C4_ADD, type4_best_total_ADD

    def __step_S7D13(self, sheet7_B6=None, sheet7_C6=None, sheet7_B8=None, sheet7_C8=None, sheet7_B4=None,
                     sheet7_C4=None, sheet6_B20=None, sheet6_B21=None, sheet7_B7=None, sheet7_C7=None,
                     sheet6_F20=None, sheet6_F21=None, sheet7_B9=None, sheet7_C9=None,sheet6_E20=None, sheet6_E21=None
                     , sheet6_D5=None, sheet6_D6=None, sheet6_D9=None,
                     sheet6_D10=None, sheet6_D11=None, sheet6_D12=None
                     , sheet6_B16=None, sheet6_C16=None, sheet6_B25=None,
                     sheet6_B26=None, sheet6_C25=None, sheet6_C26=None
                     , sheet6_C30=None, sheet6_C31=None, sheet6_B35=None,
                     sheet6_B36=None, sheet6_C35=None, sheet6_C36=None
                     , sheet2_C3=None, sheet2_C4=None, sheet2_D3=None,
                     sheet2_D4=None, sheet2_C9=None, sheet2_C10=None
                     , sheet4_B6=None, sheet4_B7=None, sheet6_D20=None,
                     sheet6_D21=None,code=None
                     ):



        # 基准值
        # sheet7_B6 = 0.8301
        # sheet7_C6 = 0.8593
        # 随机输入的两个铁水比参数
        # sheet7_B6 = 0.82
        # sheet7_C6 = 0.86
        # 根据铁水比公式得到的铁水比
        # sheet7_B6 = sheet7_B4 / (sheet7_B8 * sheet6_E20 / 1000)
        # sheet7_C6 = sheet7_C4 / (sheet7_C8 * sheet6_E21 / 1000)

        # sheet6_B16 = 62.8
        # sheet6_D11 = 1253.6
        # sheet6_D6 = 3280
        # sheet4_B6 = 0.935
        # sheet6_B35 = 3
        # sheet6_C16 = 3.5
        # sheet6_D5 = 508.5
        # sheet6_F20 = 0.966863
        # sheet6_F21 = 0.968649

        # 各种价格及参数输入画面 sheet6
        ## sheet6_C5 = 2011.842
        ## sheet6_C6 = 2190.4
        ## sheet6_C9 = 509
        ## sheet6_C10 = 2000
        ## sheet6_C11 = 703
        ## sheet6_C12 = 2170.08473641534
        # sheet6_D5 = 3710
        # sheet6_D6 = 3280
        # sheet6_D9 = 508.5
        # sheet6_D10 = 2000
        # sheet6_D11 = 1253.6
        # sheet6_D12 = 2992.78307679633
        #
        # sheet6_B16 = 62.8
        # sheet6_C16 = 3.5

        # sheet6_B20 = sheet6_J20 / 10000
        # sheet6_B21 = sheet6_J21 / 10000
        sheet6_C20 = sheet6_B20 / (sheet6_B20 + sheet6_B21)
        sheet6_C21 = sheet6_B21 / (sheet6_B20 + sheet6_B21)
        # sheet6_D20 = 0.8301
        # sheet6_D21 = 0.8593
        #
        # sheet6_B25 = 0.4005
        # sheet6_B26 = 0.4174
        # sheet6_C25 = 1349.9
        # sheet6_C26 = 1347.2
        #
        # sheet6_C30 = 1652.9
        # sheet6_C31 = 1661.7

        # sheet6_B35 = 3
        # sheet6_B36 = 3
        # sheet6_C35 = 0.2175
        # sheet6_C36 = 0.1777
        # sheet6_D20 = 0.8301
        # sheet6_D21 = 0.8593
        sheet7_B5 = sheet6_D20
        sheet7_C5 = sheet6_D21

        # 铁水温度成分停吹温度影响 sheet1
        # 铁水温度的影响 sheet1 table1
        # 一炼钢标准温度
        # sheet1_B4 = 1349.9
        sheet1_B4 = sheet6_C25
        # 二炼钢标准温度
        # sheet1_C4 = 1347.2
        sheet1_C4 = sheet6_C26
        # 预计温度
        sheet1_B5 = sheet6_C25
        sheet1_C5 = sheet6_C26
        # 温度差值
        # MIN为 -25℃？
        sheet1_B6 = sheet1_B5 - sheet1_B4
        sheet1_C6 = sheet1_C5 - sheet1_C4
        # 折算冷却能
        sheet1_B7 = 0.4 * sheet1_B6 / 10
        sheet1_C7 = 0.4 * sheet1_C6 / 10

        # 铁水Si的影响 sheet1 table2
        # 一炼钢标准Si
        # sheet1_G4 = 0.4005
        sheet1_G4 = sheet6_B25
        # 二炼钢标准Si
        # sheet1_H4 = 0.4174
        sheet1_H4 = sheet6_B26
        # 预计Si
        sheet1_G5 = sheet6_B25
        sheet1_H5 = sheet6_B26
        # Si差值
        # MIN？
        sheet1_G6 = sheet1_G5 - sheet1_G4
        sheet1_H6 = sheet1_H5 - sheet1_H4
        # 折算冷却能
        sheet1_G7 = 1.3 * sheet1_G6 * 10
        sheet1_H7 = 1.3 * sheet1_H6 * 10
        # 停吹温度的影响 sheet1 table3
        # 一炼钢标准T
        # sheet1_L4 = 1652.9
        sheet1_L4 = sheet6_C30
        # 二炼钢标准T
        # sheet1_M4 = 1661.7
        sheet1_M4 = sheet6_C31
        # 预计T
        sheet1_L5 = sheet6_C30
        sheet1_M5 = sheet6_C31
        # T差值
        # MIN？
        # 4-5?
        sheet1_L6 = sheet1_L4 - sheet1_L5
        sheet1_M6 = sheet1_M4 - sheet1_M5
        # 折算冷却能
        sheet1_L7 = 0.6 * sheet1_L6 / 10
        sheet1_M7 = 0.6 * sheet1_M6 / 10
        # 发热剂对石灰及铁损影响 sheet2
        # 加发热剂对钢铁料消耗的影响 sheet2 table1
        # sheet2_C3 = 0.18
        # sheet2_C4 = 0.18
        # sheet2_D3 = 700
        # sheet2_D4 = 700
        sheet2_E3 = sheet2_D3 * sheet2_C3 * 60 / 28
        sheet2_E4 = sheet2_D4 * sheet2_C4 * 60 / 28
        sheet2_F3 = sheet6_B35
        sheet2_F4 = sheet6_B36
        # sheet2_F3 = 3
        # sheet2_F4 = 3
        sheet2_G3 = sheet2_F3 * sheet2_E3 + sheet2_E3
        sheet2_G4 = sheet2_F4 * sheet2_E4 + sheet2_E4
        sheet2_H3 = sheet2_D3 * 0.10 * sheet2_F3 * 0.8
        sheet2_H4 = sheet2_D4 * 0.10 * sheet2_F4 * 0.8
        sheet2_I3 = sheet2_G3 + sheet2_H3
        sheet2_I4 = sheet2_G4 + sheet2_H4
        sheet2_J3 = sheet6_C35
        sheet2_J4 = sheet6_C36
        sheet2_K3 = sheet2_I3 * sheet2_J3
        sheet2_K4 = sheet2_I4 * sheet2_J4
        sheet2_L3 = sheet2_K3 / 300 * 0.9
        sheet2_L4 = sheet2_K4 / 300 * 0.9
        sheet2_M3 = sheet2_E3 * sheet2_F3 / 0.91 / 300
        sheet2_M4 = sheet2_E4 * sheet2_F4 / 0.91 / 300
        sheet2_N3 = sheet6_D12 * sheet2_L3 / 1000
        sheet2_N4 = sheet6_D12 * sheet2_L4 / 1000
        sheet2_O3 = sheet6_D9 * sheet2_M3 / 1000
        sheet2_O4 = sheet6_D9 * sheet2_M4 / 1000
        sheet2_P3 = sheet6_D10 * sheet2_D3 / 1000 / 300
        sheet2_P4 = sheet6_D10 * sheet2_D4 / 1000 / 300
        sheet2_Q3 = sheet2_N3 + sheet2_O3 + sheet2_P3
        sheet2_Q4 = sheet2_N4 + sheet2_O4 + sheet2_P4
        # 如转炉热量不足比较多，而铁水条件的波动未及时反映出来，可能造成发热剂加入量不够，吹炼后期需要依靠铁的氧化来升温，渣中（T.Fe）含量可能从正常值增加到28%，钢铁料消耗增加如下表。

        # 转炉热量不平衡引起的过吹造成的损失（单炉） sheet2 table2
        sheet2_B9 = sheet6_C35
        sheet2_B10 = sheet6_C36
        # sheet2_C9 = 0.28
        # sheet2_C10 = 0.25
        sheet2_D9 = (sheet2_C9 - sheet2_B9) * 29 * 1000 / 300
        sheet2_D10 = (sheet2_C10 - sheet2_B10) * 29 * 1000 / 300
        # 备注：炉渣量29吨/炉
        # 备注：潜在隐形成本未考虑，随着发热剂硅元素增加，喷溅加大，增加环保和钢铁料消耗成本增加。

        # 一炼钢系数 sheet2 table3
        # sheet2_A17_max = 0.87
        # sheet2_A17_min = 0.86
        # sheet2_A18_max = 0.86
        # sheet2_A18_min = 0.85
        # sheet2_A19_max = 0.85
        # sheet2_A19_min = 0.84
        # sheet2_A20_max = 0.84
        # sheet2_A20_min = 0.83
        # sheet2_A21_max = 0.83
        # sheet2_A21_min = 0.82
        # sheet2_A22_max = 0.82
        # sheet2_A22_min = 0.81
        sheet2_B17 = 0.15
        sheet2_B18 = 0.30
        sheet2_B19 = 0.40
        sheet2_B20 = 0.50
        sheet2_B21 = 0.70
        sheet2_B22 = 1.00
        sheet2_C17 = sheet2_B17 + 0.93
        sheet2_C18 = sheet2_C17 + sheet2_B18
        sheet2_C19 = sheet2_C18 + sheet2_B19
        sheet2_C20 = sheet2_C19 + sheet2_B20
        sheet2_C21 = sheet2_C20 + sheet2_B21
        sheet2_C22 = sheet2_C21 + sheet2_B22
        sheet2_D17 = 0.010
        sheet2_D18 = 0.010
        sheet2_D19 = 0.010
        sheet2_D20 = 0.010
        sheet2_D21 = 0.025
        sheet2_D22 = 0.035
        sheet2_F16 = 87
        sheet2_F17 = sheet7_B6 * 100 + 1.94 + sheet1_B7 + sheet1_G7 + sheet1_L7
        sheet2_F18 = sheet2_F16 - sheet2_F17
        if sheet2_F18 > 2 and sheet2_F18 <= 3:
            sheet2_F20 = sheet2_B19 * (sheet2_F18 - 2.05)
        elif sheet2_F18 > 3 and sheet2_F18 <= 4:
            sheet2_F20 = sheet2_C19 - sheet2_C18 - sheet2_B19 * 0.05 + sheet2_B20 * (sheet2_F18 - 3)
        elif sheet2_F18 > 4 and sheet2_F18 <= 5:
            sheet2_F20 = sheet2_C20 - sheet2_C18 - sheet2_B19 * 0.05 + sheet2_B21 * (sheet2_F18 - 4)
        elif sheet2_F18 > 5 and sheet2_F18 <= 10:
            sheet2_F20 = sheet2_C21 - sheet2_C18 - sheet2_B19 * 0.05 + sheet2_B22 * (sheet2_F18 - 5)
        elif sheet2_F18 > 1 and sheet2_F18 <= 2:
            sheet2_F20 = -sheet2_B19 * 0.05 + sheet2_B18 * (sheet2_F18 - 2)
        elif sheet2_F18 > 0 and sheet2_F18 <= 1:
            sheet2_F20 = -sheet2_B19 * 0.05 - sheet2_B18 + sheet2_B17 * (sheet2_F18 - 1)
        elif sheet2_F18 <= 0:
            sheet2_F20 = -sheet2_B19 * 0.05 - sheet2_B18 - sheet2_B17

        # 铁水比变化加硅铁碳球对吹炼铁损和石灰消耗的影响 sheet2 table4
        sheet2_I19 = sheet2_L3 * sheet2_F20
        sheet2_J19 = sheet2_M3 * sheet2_F20
        sheet2_K19 = sheet2_D3 * sheet2_F20 / 300
        sheet2_I20 = sheet6_D12 * sheet2_I19 / 1000
        sheet2_J20 = sheet6_D9 * sheet2_J19 / 1000
        sheet2_K20 = sheet6_D10 * sheet2_K19 / 1000
        sheet2_L20 = sheet2_I20 + sheet2_J20 + sheet2_K20

        # 二炼钢系数 sheet2 table5
        # sheet2_A26_max = 0.90
        # sheet2_A26_min = 0.89
        # sheet2_A27_max = 0.89
        # sheet2_A27_min = 0.88
        # sheet2_A28_max = 0.88
        # sheet2_A28_min = 0.87
        # sheet2_A29_max = 0.87
        # sheet2_A29_min = 0.86
        # sheet2_A30_max = 0.86
        # sheet2_A30_min = 0.85
        # sheet2_A31_max = 0.85
        # sheet2_A31_min = 0.84
        sheet2_B26 = 0.15
        sheet2_B27 = 0.30
        sheet2_B28 = 0.45
        sheet2_B29 = 0.60
        sheet2_B30 = 0.80
        sheet2_B31 = 1.00
        sheet2_C26 = sheet2_B26 + 0.4
        sheet2_C27 = sheet2_C26 + sheet2_B27
        sheet2_C28 = sheet2_C27 + sheet2_B28
        sheet2_C29 = sheet2_C28 + sheet2_B29
        sheet2_C30 = sheet2_C29 + sheet2_B30
        sheet2_C31 = sheet2_C30 + sheet2_B31
        sheet2_D26 = 0.010
        sheet2_D27 = 0.010
        sheet2_D28 = 0.010
        sheet2_D29 = 0.010
        sheet2_D30 = 0.025
        sheet2_D31 = 0.035
        sheet2_F25 = 90
        sheet2_F26 = sheet7_C6 * 100 + 1.59 + sheet1_C7 + sheet1_H7 + sheet1_M7
        sheet2_F27 = sheet2_F25 - sheet2_F26
        if sheet2_F27 > 2 and sheet2_F27 <= 3:
            sheet2_F29 = sheet2_B28 * (sheet2_F27 - 2.48)
        elif sheet2_F27 > 3 and sheet2_F27 <= 4:
            sheet2_F29 = sheet2_C28 - sheet2_C27 - sheet2_B28 * 0.48 + sheet2_B29 * (sheet2_F27 - 3)
        elif sheet2_F27 > 4 and sheet2_F27 <= 5:
            sheet2_F29 = sheet2_C29 - sheet2_C27 - sheet2_B28 * 0.48 + sheet2_B30 * (sheet2_F27 - 4)
        elif sheet2_F27 > 5 and sheet2_F27 <= 10:
            sheet2_F29 = sheet2_C30 - sheet2_C27 - sheet2_B28 * 0.48 + sheet2_B31 * (sheet2_F27 - 5)
        elif sheet2_F27 > 1 and sheet2_F27 <= 2:
            sheet2_F29 = -sheet2_B28 * 0.48 + sheet2_B27 * (sheet2_F27 - 2)
        elif sheet2_F27 > 0 and sheet2_F27 <= 1:
            sheet2_F29 = -sheet2_B28 * 0.48 - sheet2_B27 + sheet2_B27 * (sheet2_F27 - 1)
        elif sheet2_F27 <= 0:
            sheet2_F29 = -sheet2_B28 * 0.48 - sheet2_B27 - sheet2_B26

        # 铁水比变化加硅铁碳球对吹炼铁损和石灰消耗的影响 sheet2 table6

        sheet2_I28 = sheet2_L4 * sheet2_F29
        sheet2_J28 = sheet2_M4 * sheet2_F29
        sheet2_K28 = sheet2_D4 * sheet2_F29 / 300
        sheet2_I29 = sheet6_D12 * sheet2_I28 / 1000
        sheet2_J29 = sheet6_D9 * sheet2_J28 / 1000
        sheet2_K29 = sheet6_D10 * sheet2_K28 / 1000
        sheet2_L29 = sheet2_I29 + sheet2_J29 + sheet2_K29

        # 炉次过吹造成铁损 sheet2 table7

        sheet2_B34 = 87
        sheet2_C34 = 90
        sheet2_B35 = sheet2_F17
        sheet2_C35 = sheet2_F26
        sheet2_B36 = sheet2_B34 - sheet2_B35
        sheet2_C36 = sheet2_C34 - sheet2_C35
        if sheet2_B36 > 2 and sheet2_B36 <= 3:
            sheet2_B37 = sheet2_D19 * (sheet2_B36 - 2.05)
        elif sheet2_B36 > 3 and sheet2_B36 <= 4:
            sheet2_B37 = sheet2_D19 * 0.95 + sheet2_D20 * (sheet2_B36 - 3)
        elif sheet2_B36 > 4 and sheet2_B36 <= 5:
            sheet2_B37 = sheet2_D19 * 0.95 + sheet2_D20 + sheet2_D21 * (sheet2_B36 - 4)
        elif sheet2_B36 > 5 and sheet2_B36 <= 10:
            sheet2_B37 = sheet2_D19 * 0.95 + sheet2_D20 + sheet2_D21 + sheet2_D22 * (sheet2_B36 - 5)
        elif sheet2_B36 > 1 and sheet2_B36 <= 2:
            sheet2_B37 = -sheet2_D19 * 0.05 + sheet2_D18 * (sheet2_B36 - 2)
        elif sheet2_B36 > 0 and sheet2_B36 <= 1:
            sheet2_B37 = -sheet2_D19 * 0.05 - sheet2_D18 + sheet2_D17 * (sheet2_B36 - 1)
        elif sheet2_B36 <= 0:
            sheet2_B37 = -sheet2_D19 * 0.05 - sheet2_D18 - sheet2_D17

        if sheet2_C36 > 2 and sheet2_C36 <= 3:
            sheet2_C37 = sheet2_D28 * (sheet2_C36 - 2.48)
        elif sheet2_C36 > 3 and sheet2_C36 <= 4:
            sheet2_C37 = sheet2_D28 * 0.52 + sheet2_D29 * (sheet2_C36 - 3)
        elif sheet2_C36 > 4 and sheet2_C36 <= 5:
            sheet2_C37 = sheet2_D28 * 0.52 + sheet2_D29 + sheet2_D30 * (sheet2_C36 - 4)
        elif sheet2_C36 > 5 and sheet2_C36 <= 10:
            sheet2_C37 = sheet2_D28 * 0.52 + sheet2_D29 + sheet2_D30 + sheet2_D31 * (sheet2_C36 - 5)
        elif sheet2_C36 > 1 and sheet2_C36 <= 2:
            sheet2_C37 = -sheet2_D28 * 0.48 + sheet2_D27 * (sheet2_C36 - 2)
        elif sheet2_C36 > 0 and sheet2_C36 <= 1:
            sheet2_C37 = -sheet2_D28 * 0.48 - sheet2_D27 + sheet2_D26 * (sheet2_C36 - 1)
        elif sheet2_C36 <= 0:
            sheet2_C37 = -sheet2_D28 * 0.48 - sheet2_D27 - sheet2_D26

        sheet2_B38 = sheet2_D9 * sheet2_B37 * sheet6_D12 / 300
        sheet2_C38 = sheet2_D10 * sheet2_C37 * sheet6_D12 / 300

        # 矿石用量影响 sheet3
        # 铁水比降低1%，转炉入炉原料热值减少，废钢用量增加，矿石投入减少 sheet3 table1
        sheet3_B3 = -320 / (3.3 * 100) / 300
        sheet3_B4 = -sheet3_B3 * sheet6_B16 / 100 * (1 - sheet6_C35) / sheet4_B6
        sheet3_C3 = sheet6_D11
        sheet3_C4 = sheet6_D6
        sheet3_D3 = sheet3_B3 * sheet3_C3
        sheet3_D4 = sheet3_B4 * sheet3_C4
        sheet3_D5 = sheet3_D3 + sheet3_D4
        # 备注：装入量按照320吨计算

        # sheet3 table2
        sheet3_C8 = 87
        sheet3_D8 = 90
        sheet3_C9 = sheet2_F17
        sheet3_D9 = sheet2_F26
        sheet3_C10 = sheet3_C8 - sheet3_C9
        sheet3_D10 = sheet3_D8 - sheet3_D9
        if sheet3_C10 >= 2.07:
            sheet3_C12 = 0
        elif sheet3_C10 < 2.07:
            sheet3_C12 = 0

        # 一、二炼钢分别以87%、90%铁水比为基准
        if sheet3_D10 > 2.48 and sheet3_D10 <= 3:
            sheet3_D12 = sheet3_D5 * (sheet3_D10 - 2.48)
        elif sheet3_D10 > 3:
            sheet3_D12 = sheet3_D5 * (3 - 2.48)
        elif sheet3_D10 <= 2.48:
            sheet3_D12 = 0

        # 一、二炼钢分别以制造部数据83.01%、85.93%铁水比为基准

        # 铁水比变化1%引起的矿石量变化对钢铁料消耗和石灰消耗的影响 sheet3 table3
        sheet3_A16 = 320 / (3.3 * 100) * 1000
        sheet3_B16 = sheet6_C16
        sheet3_C16 = sheet6_B35
        sheet3_D16 = sheet3_A16 * sheet3_B16 / 100 * (sheet3_C16 + 1)
        sheet3_E16 = sheet6_C35
        sheet3_F16 = sheet3_D16 * sheet3_E16
        sheet3_G16 = sheet3_A16 * sheet6_B16 / 100 * (1 - sheet3_E16)
        sheet3_H16 = -(sheet3_G16 - sheet3_F16) / 300
        sheet3_I16 = sheet3_A16 * sheet3_B16 / 100 * sheet3_C16 / 0.91 / 300
        sheet3_J16 = sheet6_D12 * sheet3_H16 / 1000
        sheet3_K16 = sheet6_D9 * sheet3_I16 / 1000
        sheet3_L16 = sheet6_D11 * sheet3_A16 / 1000 / 300
        sheet3_M16 = sheet3_J16 + sheet3_K16 + sheet3_L16

        # sheet3 table4
        sheet3_C19 = 87
        sheet3_D19 = 90
        sheet3_C20 = sheet2_F17
        sheet3_D20 = sheet2_F26
        sheet3_C21 = sheet3_C20 - sheet3_C19
        sheet3_D21 = sheet3_D20 - sheet3_D19

        if sheet3_C21 > -2 and sheet3_C21 < 0:
            sheet3_C26 = sheet3_J16 * (2 + sheet3_C21 - sheet2_F20)
        elif sheet3_C21 >= 0:
            sheet3_C26 = sheet3_J16 * (2 + sheet3_C21)
        elif sheet3_C21 <= -2:
            sheet3_C26 = 0

        if sheet3_D21 > -2.4799 and sheet3_D21 < 0:
            sheet3_D26 = sheet3_J16 * (2.48 + sheet3_D21 - sheet2_F29)
        elif sheet3_D21 >= 0:
            sheet3_D26 = sheet3_J16 * (2.48 + sheet3_D21)
        elif sheet3_D21 <= -2.4799:
            sheet3_D26 = 0

        if sheet3_C21 > -2 and sheet3_C21 < 0:
            sheet3_C27 = sheet3_K16 * (2 + sheet3_C21 - sheet2_F20)
        elif sheet3_C21 >= 0:
            sheet3_C27 = sheet3_K16 * (2 + sheet3_C21)
        elif sheet3_C21 <= -2:
            sheet3_C27 = 0

        if sheet3_D21 > -2.4799 and sheet3_D21 < 0:
            sheet3_D27 = sheet3_K16 * (2.48 + sheet3_D21 - sheet2_F29)
        elif sheet3_D21 >= 0:
            sheet3_D27 = sheet3_K16 * (2.48 + sheet3_D21)
        elif sheet3_D21 <= -2.4799:
            sheet3_D27 = 0

        if sheet3_C21 > -2 and sheet3_C21 < 0:
            sheet3_C28 = sheet3_L16 * (2 + sheet3_C21 - sheet2_F20)
        elif sheet3_C21 >= 0:
            sheet3_C28 = sheet3_L16 * (2 + sheet3_C21)
        elif sheet3_C21 <= -2:
            sheet3_C28 = 0

        if sheet3_D21 > -2.4799 and sheet3_D21 < 0:
            sheet3_D28 = sheet3_L16 * (2.48 + sheet3_D21 - (sheet2_C26 + sheet2_F29))
        elif sheet3_D21 >= 0:
            sheet3_D28 = sheet3_L16 * (2.48 + sheet3_D21)
        elif sheet3_D21 <= -2.4799:
            sheet3_D28 = 0

        sheet3_C29 = sheet3_C26 + sheet3_C27 + sheet3_C28
        sheet3_D29 = sheet3_D26 + sheet3_D27 + sheet3_D28

        # 以上两项合计影响 sheet3 table5

        sheet3_C37 = sheet3_C12 + sheet3_C26
        sheet3_D37 = sheet3_D12 + sheet3_D26
        sheet3_C38 = sheet3_C27
        sheet3_D38 = sheet3_D27
        sheet3_C39 = sheet3_C28
        sheet3_D39 = sheet3_D28

        # 铁水废钢价格影响 sheet4
        # 转炉影响测算=转炉原料结构（成本上升）+矿石投入减少废钢增加（成本上升） +转炉煤气回收减少（成本上升）+发热剂用量增加（成本上升）+钢铁料消耗上升+辅料成本上升
        # 1、铁水比每降低1%，外购废钢和铁水比等量代换后，性价比影响。 sheet4 table1

        # sheet4_B6 = 0.935
        # sheet4_B7 = 0.945
        sheet4_C6 = sheet6_D6
        sheet4_C7 = sheet6_D5
        sheet4_D6 = sheet4_C6 / sheet4_B6
        sheet4_D7 = sheet4_C7 / sheet4_B7
        sheet4_E6 = sheet4_D6 - sheet4_D7
        sheet4_F6 = sheet4_E6 * 3.2 / 300
        # 装入量320吨，产出量300吨

        # sheet4 table2
        sheet4_C10 = sheet6_D20 * 100
        sheet4_D10 = sheet6_D21 * 100
        # sheet4_C10 = 83.01
        # sheet4_D10 = 85.93
        # 一、二炼钢分别以制造部数据83.01%、85.93%铁水比为基准
        sheet4_C11 = sheet7_B6 * 100
        sheet4_D11 = sheet7_C6 * 100
        sheet4_C12 = sheet4_C10 - sheet4_C11
        sheet4_D12 = sheet4_D10 - sheet4_D11
        sheet4_C13 = sheet4_F6 * sheet4_C12
        sheet4_D13 = sheet4_F6 * sheet4_D12

        # 过程数据汇总 sheet5
        # 铁水比变化对各项成本的影响汇总
        # 铁水比降低，可能引起耐材消耗增加，这部分成本暂不考虑。
        # （1）一炼钢成本影响值汇总 sheet5 table1
        sheet5_C6 = sheet4_C13
        sheet5_G6 = sheet5_C6
        sheet5_C7 = sheet3_C37
        sheet5_D7 = sheet3_C39
        sheet5_G7 = sheet5_C7 + sheet5_D7
        sheet5_D8 = sheet2_K20
        sheet5_F8 = sheet2_I20
        sheet5_G8 = sheet5_D8 + sheet5_F8
        sheet5_D9 = sheet2_J20 + sheet3_C38
        sheet5_G9 = sheet5_D9
        sheet5_F10 = sheet2_B38
        sheet5_G10 = sheet5_F10
        sheet5_G11 = sheet5_G6 + sheet5_G7 + sheet5_G8 + sheet5_G9 + sheet5_G10

        # （2）二炼钢成本影响值汇总 sheet5 table2
        sheet5_C15 = sheet4_D13
        sheet5_G15 = sheet5_C15
        sheet5_C16 = sheet3_D37
        sheet5_D16 = sheet3_D39
        sheet5_G16 = sheet5_C16 + sheet5_D16
        sheet5_D17 = sheet2_K29
        sheet5_F17 = sheet2_I29
        sheet5_G17 = sheet5_D17 + sheet5_F17
        sheet5_D18 = sheet2_J29 + sheet3_D38
        sheet5_G18 = sheet5_D18
        sheet5_F19 = sheet2_C38
        sheet5_G19 = sheet5_F19
        sheet5_G20 = sheet5_G15 + sheet5_G16 + sheet5_G17 + sheet5_G18 + sheet5_G19

        # 计算总结果 sheet7
        # 铁水比变动对效益的影响
        # 说明：下表输入拟铁水比值就直接得出结果；其它有底色的不能修改。 sheet7 table1

        # sheet7_D4 = sheet7_B4 + sheet7_C4
        # # sheet7_B5 = sheet4_C10 / 100
        # # sheet7_C5 = sheet4_D10 / 100
        # # sheet7_B5 = 0.8301
        # # sheet7_C5 = 0.8593
        #
        # sheet7_D7 = sheet7_B7 + sheet7_C7
        #
        # sheet7_D8 = sheet7_B8 + sheet7_C8
        # 铁水比变化炼钢成本变化结果 sheet7 table2

        sheet7_B13 = sheet5_G11 * sheet7_B8
        sheet7_C13 = sheet5_G20 * sheet7_C8
        sheet7_D13 = sheet7_B13 + sheet7_C13
        # 正数为增加成本，负数为降低成本

        sheet7_B14 = sheet7_B13 / sheet7_B8
        sheet7_C14 = sheet7_C13 / sheet7_C8
        sheet7_D14 = sheet7_B14 + sheet7_C14
        # 正数为增加成本，负数为降低成本
        if code == 0:
            return sheet7_D13


        if code == 1:
            return sheet7_B5, sheet7_C5, sheet7_B13, sheet7_C13, sheet7_D13, sheet7_B14, sheet7_C14, sheet7_D14


